﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Customers : System.Web.UI.Page
{

    SqlConnection con = new SqlConnection();
    SqlDataAdapter da;
    DataSet ds;
    SqlCommandBuilder cb;
    DataTable dt;
    DataView dv;
    protected void Page_Load(object sender, EventArgs e)
    {
       
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = constr;


        validationInput(false);
        if (!IsPostBack)
        {
            FillCustomers();
            InitializeADOOObject();
           
        }

       
    }
    void FillCustomers()
    {
        SqlConnection con = new SqlConnection();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = constr;
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Sales.Customers", con);
        dt = new DataTable("Sales.Customers");
        //DataSet ds = new DataSet();
        da.Fill(dt);
        gvCustomers.AutoGenerateSelectButton = true;
        gvCustomers.AllowPaging = true;
        gvCustomers.PageSize = 10;
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();


    }


    void InitializeADOOObject()
    {

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "getCustomerTable";

        da = new SqlDataAdapter(cmd);
        cb = new SqlCommandBuilder(da);

        ds = new DataSet();
        da.FillSchema(ds, SchemaType.Mapped);
        da.Fill(ds, "Sales.Customers");
    }
    private void validationInput(bool b)
    {
        txtCopname.Enabled = b;
        txtConname.Enabled = b;
        txtConTitle.Enabled = b;
        txtAddress.Enabled = b;
        txtCity.Enabled = b;
        txtRegion.Enabled = b;
        txtPostalcode.Enabled = b;
        DDLCountry.Enabled = b;
        txtPhone.Enabled = b;
        txtFax.Enabled = b;
    }
    private void EnableComponent(bool b)
    {
        try
        {
            txtCopname.Enabled = b;
            txtConname.Enabled = b;
            txtConTitle.Enabled = b;
            txtAddress.Enabled = b;
            txtCity.Enabled = b;
            txtRegion.Enabled = b;
            txtPostalcode.Enabled = b;
            DDLCountry.Enabled = b;
            txtPhone.Enabled = b;
            txtFax.Enabled = b;
        }
        catch (Exception)
        { }
    }
    void addCustomers()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "addCustomerTable";
            SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
            param.Value = (txtCopname.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
            param.Value = (txtConname.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
            param.Value = (txtConTitle.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
            param.Value = (txtAddress.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
            param.Value = (txtCity.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
            param.Value = (txtRegion.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
            param.Value = (txtPostalcode.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
            param.Value = (DDLCountry.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
            param.Value = (txtPhone.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
            param.Value = (txtFax.Text);
            cmd.Parameters.Add(param);



            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('ADD SUCCESSFULLY!'); </script>");
            con.Close();
        }
        catch (Exception ex)
        {

            Response.Write("<script language='JavaScript'> alert('CANNOT ADD') ;</script>");
        }

        FillCustomers();
        InitializeADOOObject();
        //try
        //{

        //    DataTable dt = ds.Tables["Sales.Customers"];
        //    DataRow r = dt.NewRow();
        //    r["companyname"] = txtCopname.Text;
        //    r["contactname"] = txtConname.Text;
        //    r["contacttitle"] = txtConTitle.Text;
        //    r["address"] = txtAddress.Text;
        //    r["city"] = txtCity.Text;
        //    r["region"] = txtRegion.Text;
        //    r["postalcode"] = txtPostalcode.Text;
        //    r["country"] = DDLCountry.Text;
        //    r["phone"] = txtPhone.Text;
        //    r["fax"] = txtFax.Text;
        //    dt.Rows.Add(r);
        //    da.Update(ds, "Sales.Customers");
        //    FillCustomers();
        //    InitializeADOOObject();
        //    Response.Write("<script language='JavaScript'> alert('CONGRATULATION ! CUSTOMERS HAS BEEN SAVED !'); </script>");
        //}
        //catch (Exception)
        //{8

        //    Response.Write("<script language='JavaScript'> alert('SORRY ! SAVE CUSTOMER FAILED! '); </script>");
        //}
       

    }


    void deleteCustomers()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "deleteCustomerTable";
            SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
            param.Value = int.Parse(txtID.Text);
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
            FillCustomers();
            InitializeADOOObject();
            Response.Write("<script language='JavaScript'> alert('DELETE SUCCESSFULL !'); </script>");

        }
        catch (System.Exception ex)
        {
            Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT DELETE THIS ROW'); </script>");
        }
       
       

      

    }
    void newCustomer()
    {
        EnableComponent(true);
        txtID.Text = "";
        txtCopname.Text = "";
        txtConname.Text = "";
        txtConTitle.Text = "";
        txtAddress.Text = "";
        txtCity.Text = "";
        txtRegion.Text = "";
        txtPostalcode.Text = "";
        DDLCountry.SelectedItem.Text = "";
        txtPhone.Text = "";
        txtFax.Text = "";
        txtSearch.Text = "";
        DDLContactTitle.SelectedItem.Text = "";
        validationInput(true);
    }
    void updateCustomers()
    {

        try
        {

            SqlConnection con = new SqlConnection();
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.ConnectionString = constr;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            string sql = string.Format("UPDATE Sales.Customers SET companyname='{0}',contactname='{1}',contacttitle='{2}',address='{3}',city='{4}'," +
                                        "region='{5}',postalcode='{6}',country='{7}',phone='{8}',fax='{9}' WHERE custid='{10}'",
                                        txtCopname.Text, txtConname.Text, txtConTitle.Text, txtAddress.Text, txtCity.Text, txtRegion.Text, txtPostalcode.Text,
                                        DDLCountry.Text, txtPhone.Text.ToString(), txtFax.Text.ToString(), txtID.Text.ToString());
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
   
            con.Close();
            InitializeADOOObject();
            FillCustomers(); 
            Response.Write("<script language='JavaScript'> alert('UPDATE SUCCESSFULL !'); </script>");
        }
        catch (Exception ex)
        {

            Response.Write("<script language='JavaScript'> alert('SORRY ! THIS ITEM CANNOT UPDATE;'); </script>");
        }
       
    }

    protected void gvCustomers_SelectedIndexChanged(object sender, EventArgs e)
    {
        validationInput(true);
        EnableComponent(true);


        int index = gvCustomers.SelectedRow.RowIndex;
        Session["index"] = index;
        GridViewRow r = gvCustomers.SelectedRow;
        txtID.Text = r.Cells[1].Text;
        txtCopname.Text = Server.HtmlDecode(r.Cells[2].Text);
        txtConname.Text = Server.HtmlDecode(r.Cells[3].Text);
        txtConTitle.Text = Server.HtmlDecode(r.Cells[4].Text);
        txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text);
        txtCity.Text = Server.HtmlDecode(r.Cells[6].Text);
        txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text);
        txtPostalcode.Text = Server.HtmlDecode(r.Cells[8].Text);
        DDLCountry.SelectedItem.Text = Server.HtmlDecode(r.Cells[9].Text);
        txtPhone.Text = Server.HtmlDecode(r.Cells[10].Text);
        txtFax.Text = Server.HtmlDecode(r.Cells[11].Text);
       
        

    }
    protected void gvCustomers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        FillCustomers();
        InitializeADOOObject();
    
        try
        {
            if ((bool)Session["SearchCustomerActive"] == false)
            {
                gvCustomers.PageIndex = e.NewPageIndex;
                gvCustomers.DataBind();
            }
            else
            {
                gvCustomers.PageIndex = e.NewPageIndex;
                dv = new DataView(dt);
                dv.RowFilter = string.Format("companyname like '%{0}%' ", txtSearch.Text);
                gvCustomers.DataSource = dv;
                gvCustomers.DataBind();

            }

            if ((bool)Session["FilterTitleActive"] == false)
            {
                gvCustomers.PageIndex = e.NewPageIndex;
                gvCustomers.DataBind();
            }
            else
            {
                gvCustomers.PageIndex = e.NewPageIndex;
                dv = new DataView(dt);
                dv.RowFilter = string.Format("contacttitle like '%{0}%' ", DDLContactTitle.Text);
                gvCustomers.DataSource = dv;
                gvCustomers.DataBind();

            }
        }
        catch (Exception ex)
        {
            Response.Write("<script language='JavaScript'> alert('SORRY ! YOU CANNOT SELECT PAGES;'); </script>");
        }


    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        
        addCustomers();
       
       
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteCustomers();
        FillCustomers();
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        newCustomer();
       
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        updateCustomers();
        FillCustomers();
    }
    void filterbytitle()
    {
        FillCustomers();
        InitializeADOOObject();
        dv = new DataView(dt);
        dv.RowFilter = string.Format("contacttitle like '%{0}%' ", DDLContactTitle.Text);
        gvCustomers.DataSource = dv;
        gvCustomers.DataBind();
    }

    void searchbyname()
    {
        FillCustomers();
        InitializeADOOObject();
    

        dv = new DataView(dt);
        dv.RowFilter = string.Format("companyname like '%{0}%' ", txtSearch.Text);
        gvCustomers.DataSource = dv;
        gvCustomers.DataBind();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        
        Session["SearchCustomerActive"] = true;
        searchbyname();
       
    }
    protected void btnFilter_Click(object sender, EventArgs e)
    {
        Session["FilterTitleActive"] = true;
        filterbytitle();
     
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        FillCustomers();
        InitializeADOOObject();
        Session["SearchCustomerActive"] = false;
        Session["FilterTitleActive"] = false;

    }
}
